{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1.1.1 智能医疗系统中的业务数据处理流程设计\n",
    "\n",
    "本脚本演示如何从PostgreSQL数据库读取患者数据，支持环境变量配置。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 导入必要的库\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import sys\n",
    "from sqlalchemy import create_engine\n",
    "import psycopg2\n",
    "from dotenv import load_dotenv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "postgresql://hbu:********@127.0.0.1:2345/hbu\n",
      "环境变量加载完成！\n"
     ]
    }
   ],
   "source": [
    "# 加载环境变量\n",
    "load_dotenv()\n",
    "\n",
    "# 从环境变量获取数据库连接信息，如果不存在则使用默认值\n",
    "driver=os.getenv('DRIVER', 'postgresql')\n",
    "user=os.getenv('PGUSER', None)\n",
    "password=os.getenv('PGPASSWORD', None)\n",
    "host=os.getenv('PGHOST', None)\n",
    "port=os.getenv('PGPORT', None)\n",
    "database=os.getenv('PGDATABASE', 'postgres')\n",
    "\n",
    "# schema is used for postgres, similiar with database level in MySQL\n",
    "schema=os.getenv('SCHEMA',\"public\")\n",
    "\n",
    "DATABASE_URL = f\"{driver}://{user}:{password}@{host}:{port}/{database}\"\n",
    "\n",
    "if user is not None and password is not None:\n",
    "    print(f\"{driver}://{user}:********@{host}:{port}/{database}\")\n",
    "else:\n",
    "    print('非法的数据库连接URL')\n",
    "    sys.exit(1)\n",
    "print('环境变量加载完成！')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "数据库连接引擎创建成功！\n",
      "成功读取数据，共1000条记录\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Index(['PatientID', 'Age', 'BMI', 'BloodPressure', 'Cholesterol',\n",
       "       'DaysInHospital'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 创建数据库连接引擎\n",
    "try:\n",
    "    engine = create_engine(DATABASE_URL)\n",
    "    print('数据库连接引擎创建成功！')\n",
    "except Exception as e:\n",
    "    print(f'创建数据库连接引擎失败: {e}')\n",
    "    raise\n",
    "# 从PostgreSQL数据库读取数据\n",
    "try:\n",
    "    # 查询public模式下的patient_data表\n",
    "    query = f'SELECT \"PatientID\", \"Age\", \"BMI\", \"BloodPressure\", \"Cholesterol\", \"DaysInHospital\" FROM public.patient_data'\n",
    "    data = pd.read_sql(query, engine)\n",
    "    print(f'成功读取数据，共{len(data)}条记录')\n",
    "except Exception as e:\n",
    "    print(f'读取数据失败: {e}')\n",
    "data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "数据基本信息:\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1000 entries, 0 to 999\n",
      "Data columns (total 6 columns):\n",
      " #   Column          Non-Null Count  Dtype  \n",
      "---  ------          --------------  -----  \n",
      " 0   PatientID       1000 non-null   float64\n",
      " 1   Age             1000 non-null   float64\n",
      " 2   BMI             1000 non-null   float64\n",
      " 3   BloodPressure   1000 non-null   float64\n",
      " 4   Cholesterol     1000 non-null   float64\n",
      " 5   DaysInHospital  1000 non-null   float64\n",
      "dtypes: float64(6)\n",
      "memory usage: 47.0 KB\n",
      "None\n",
      "\n",
      "数据前5行:\n",
      "   PatientID   Age   BMI  BloodPressure  Cholesterol  DaysInHospital\n",
      "0        1.0  62.0  38.3          150.0        211.0             2.0\n",
      "1        2.0  65.0  34.1          118.0        243.0             8.0\n",
      "2        3.0  82.0  22.8          114.0        177.0             8.0\n",
      "3        4.0  85.0  37.2          154.0        237.0             8.0\n",
      "4        5.0  85.0  32.4          120.0        149.0            20.0\n"
     ]
    }
   ],
   "source": [
    "# 显示数据基本信息\n",
    "print('数据基本信息:')\n",
    "print(data.info())\n",
    "\n",
    "print('\\n数据前5行:')\n",
    "print(data.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "高风险患者数量: 413\n",
      "低风险患者数量: 587\n",
      "高风险患者占比: 0.413\n",
      "低风险患者占比: 0.587\n"
     ]
    }
   ],
   "source": [
    "# 数据分析和处理示例（参考1.1.1.ipynb）\n",
    "\n",
    "# 1. 统计住院天数超过7天的患者数量及其占比\n",
    "data['RiskLevel'] = np.where(data['DaysInHospital'] > 7, '高风险患者', '低风险患者')\n",
    "risk_counts = data['RiskLevel'].value_counts()\n",
    "high_risk_ratio = risk_counts['高风险患者'] / len(data)\n",
    "low_risk_ratio = risk_counts['低风险患者'] / len(data)\n",
    "\n",
    "print(\"高风险患者数量:\", risk_counts['高风险患者'])\n",
    "print(\"低风险患者数量:\", risk_counts['低风险患者'])\n",
    "print(\"高风险患者占比:\", high_risk_ratio)\n",
    "print(\"低风险患者占比:\", low_risk_ratio)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "BMI区间中高风险患者的比例和患者数:\n",
      "BMIRange\n",
      "偏瘦    0.444444\n",
      "正常    0.406699\n",
      "超重    0.388235\n",
      "肥胖    0.415094\n",
      "Name: RiskLevel, dtype: float64\n",
      "BMIRange\n",
      "肥胖    477\n",
      "正常    209\n",
      "超重    170\n",
      "偏瘦    144\n",
      "Name: count, dtype: int64\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/mh/l6_b7c0x7m3bq41r5m25snqc0000gn/T/ipykernel_40901/3360497317.py:6: FutureWarning: The default of observed=False is deprecated and will be changed to True in a future version of pandas. Pass observed=False to retain current behavior or observed=True to adopt the future default and silence this warning.\n",
      "  bmi_risk_rate = data.groupby(data['BMIRange'])['RiskLevel'].apply(lambda x: (x == '高风险患者').mean())\n"
     ]
    }
   ],
   "source": [
    "# 2. 统计不同BMI区间中高风险患者的比例\n",
    "bmi_bins = [0, 18.5, 24, 28, np.inf]\n",
    "bmi_labels = ['偏瘦', '正常', '超重', '肥胖']\n",
    "data['BMIRange'] = pd.cut(data['BMI'], bins=bmi_bins, labels=bmi_labels, right=False)\n",
    "\n",
    "bmi_risk_rate = data.groupby(data['BMIRange'])['RiskLevel'].apply(lambda x: (x == '高风险患者').mean())\n",
    "bmi_patient_count = data['BMIRange'].value_counts()\n",
    "\n",
    "print(\"BMI区间中高风险患者的比例和患者数:\")\n",
    "print(bmi_risk_rate)\n",
    "print(bmi_patient_count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "年龄区间中高风险患者的比例和患者数:\n",
      "AgeRange\n",
      "≤25岁      0.456693\n",
      "26-35岁    0.398496\n",
      "36-45岁    0.386364\n",
      "46-55岁    0.444444\n",
      "56-65岁    0.401575\n",
      "＞65岁      0.401254\n",
      "Name: RiskLevel, dtype: float64\n",
      "AgeRange\n",
      "＞65岁      319\n",
      "46-55岁    162\n",
      "26-35岁    133\n",
      "36-45岁    132\n",
      "≤25岁      127\n",
      "56-65岁    127\n",
      "Name: count, dtype: int64\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/mh/l6_b7c0x7m3bq41r5m25snqc0000gn/T/ipykernel_40901/2701959017.py:6: FutureWarning: The default of observed=False is deprecated and will be changed to True in a future version of pandas. Pass observed=False to retain current behavior or observed=True to adopt the future default and silence this warning.\n",
      "  age_risk_rate = data.groupby(data['AgeRange'])['RiskLevel'].apply(lambda x: (x == '高风险患者').mean())\n"
     ]
    }
   ],
   "source": [
    "# 3. 统计不同年龄区间中高风险患者的比例\n",
    "age_bins = [0, 26, 36, 46, 56, 66, np.inf]\n",
    "age_labels = ['≤25岁', '26-35岁', '36-45岁', '46-55岁', '56-65岁', '＞65岁']\n",
    "data['AgeRange'] = pd.cut(data['Age'], bins=age_bins, labels=age_labels, right=False)\n",
    "\n",
    "age_risk_rate = data.groupby(data['AgeRange'])['RiskLevel'].apply(lambda x: (x == '高风险患者').mean())\n",
    "age_patient_count = data['AgeRange'].value_counts()\n",
    "\n",
    "print(\"年龄区间中高风险患者的比例和患者数:\")\n",
    "print(age_risk_rate)\n",
    "print(age_patient_count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "数据库连接已关闭\n"
     ]
    }
   ],
   "source": [
    "# 关闭数据库连接\n",
    "if 'engine' in locals():\n",
    "    engine.dispose()\n",
    "    print('数据库连接已关闭')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.13.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
